SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
GO

PRINT '---- tblpersons'

PRINT '-- Table title'

INSERT tblUFGTableTitles (utt_Tablename, utt_Languagename, utt_Title, utt_Description)
VALUES ('tblpersons','Nederlands','Leden','Leden gegevens')

PRINT '-- Column titles'

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblPersons','prs_Lastname','Nederlands','Achternaam','U dient een herkenbare achternaam in te vullen.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblPersons','prs_MiddleName','Nederlands','Tussenvoegsel',NULL)

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblPersons','prs_ID','Nederlands','Lidnummer','U dient een lidnummer in te vullen.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblPersons','prs_Firstname','Nederlands','Voornaam','U dient een voornaam in te vullen.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblPersons','prs_Initials','Nederlands','Voorletters','U dient voorletters in te vullen.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblPersons','prs_Sexe','Nederlands','Geslacht','U dient een geslacht in te vullen.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblPersons','prs_DateofBirth','Nederlands','Geboortedatum','U dient een geboortedatum in te vullen.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblPersons','prs_Telephone','Nederlands','Telefoonnummer','U dient een telefoonnummer in te vullen.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblPersons','prs_Email','Nederlands','E-mail','U dient een e-mailadres in te vullen.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblPersons','prs_FamilyClubName','Nederlands','Clubnaam','U dient een clubnaam in te vullen.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblPersons','prs_FamilyName','Nederlands','Familie Naam','U dient een familie naam in te vullen.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblPersons','prs_FederationNr','Nederlands','Bondsnummer','U dient een bondsnummer in te vullen.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblPersons','prs_Remarks','Nederlands','Opmerkingen','U kunt hier opmerkingen invullen.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblPersons','prs_RefereeNr','Nederlands','Scheidsrechtersnummer','U dient een scheidsrechter bondsnummer in te vullen.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblPersons','prs_Referee','Nederlands','Scheidsrechter','Persoon is actief als scheidsrechter.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblPersons','prs_Username','Nederlands','Gebruikersnaam','U dient een gebruikersnaam in te vullen.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblPersons','prs_State','Nederlands','Status lid','U dient een status in te vullen.')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblPersons','prs_Active','Nederlands','Lid actief','U dient op te geven of het lid actief is.')

PRINT '---- HUMAN KEYS'

INSERT tblUFGHumanKeyColumns 
(uhc_TableName,uhc_ColumnName,uhc_SequenceNumber,uhc_AscendingOrder, 
uhc_ConcatenationChar, uhc_sortOrder)
VALUES ('tblPersons','prs_FamilyName',2,1,' ',2)
INSERT tblUFGHumanKeyColumns 
(uhc_TableName,uhc_ColumnName,uhc_SequenceNumber,uhc_AscendingOrder, 
uhc_ConcatenationChar, uhc_sortOrder)
VALUES ('tblPersons','prs_Firstname',3,1,' ',2)
INSERT tblUFGHumanKeyColumns 
(uhc_TableName,uhc_ColumnName,uhc_SequenceNumber,uhc_AscendingOrder, 
uhc_ConcatenationChar, uhc_sortOrder)
VALUES ('tblPersons','prs_Middlename',4,1,',',3)
INSERT tblUFGHumanKeyColumns 
(uhc_TableName,uhc_ColumnName,uhc_SequenceNumber,uhc_AscendingOrder, 
uhc_ConcatenationChar, uhc_sortOrder)
VALUES ('tblPersons','prs_Lastname',5,1,' ',4)

UPDATE tblUFGHumanKeyColumns 
SET uhc_VisibleInResolve = 'false' 
WHERE uhc_ColumnName = 'prs_ID' 

UPDATE tblUFGColumns SET ucl_PanelNumber = NULL WHERE ucl_TableName = 'tblpersons'
DELETE tblUFGPanelTitles WHERE upt_TableName = 'tblpersons'
DELETE tblUFGPanels WHERE upn_TableName = 'tblpersons'

UPDATE tblUFGTables 
SET utb_NrOfColumns = 1
WHERE utb_TableName = 'tblPersons' 

INSERT INTO dbo.tblUFGPanels (upn_TableName,upn_PanelNumber,upn_SequenceNumber,upn_NrOfColumns,upn_RowSpan)
VALUES ('tblPersons',1,1,5,1)
INSERT INTO dbo.tblUFGPanels (upn_TableName,upn_PanelNumber,upn_SequenceNumber,upn_NrOfColumns,upn_RowSpan,upn_ShowConditionTable,upn_ShowConditionColumn,upn_ShowConditionColumnValue,upn_ShowConditionNoMatch)
VALUES ('tblPersons',2,2,4,1,'tblpersons','prs_Referee','1','HideAll')


INSERT INTO dbo.tblUFGPanels (upn_TableName,upn_PanelNumber,upn_SequenceNumber,upn_NrOfColumns,upn_RowSpan)
VALUES ('tblPersons',3,3,4,1)


INSERT INTO dbo.tblUFGPanels (upn_TableName,upn_PanelNumber,upn_SequenceNumber,upn_NrOfColumns,upn_RowSpan)
VALUES ('tblPersons',4,4,4,1)

UPDATE tblUFGColumns 
SET ucl_PanelNumber = 1
WHERE ucl_TableName = 'tblPersons' 
AND ucl_ColumnName IN (
	'prs_ID',
	'prs_Lastname',
	'prs_MiddleName',
	'prs_Firstname',
	'prs_Initials',
	'prs_Sexe',
	'prs_DateofBirth',
	'prs_Telephone',
	'prs_Email',
	'prs_FamilyClubName',
	'prs_FamilyName',
	'prs_Referee',
	'prs_State',
	'prs_Active',
	'prs_Username'
)

UPDATE tblUFGColumns 
SET ucl_PanelNumber = 2
WHERE ucl_TableName = 'tblPersons' 
AND ucl_ColumnName = 'prs_RefereeNr'

UPDATE tblUFGColumns 
SET ucl_PanelNumber = 3
WHERE ucl_TableName = 'tblPersons' 
AND ucl_ColumnName = 'prs_FederationNr'

UPDATE tblUFGColumns 
SET ucl_PanelNumber = 4
WHERE ucl_TableName = 'tblPersons' 
AND ucl_ColumnName = 'prs_Remarks'

PRINT '-- Row Rights'

INSERT tblUFGRowRights
(urr_TableName,urr_RoleName,urr_SQLSelectExpression,urr_SQLInsertExpression,urr_SQLUpdateExpression,urr_SQLDeleteExpression)
SELECT 'tblPersons',urr_Rolename,'prs_FamilyClubname = dbo.fnGetClubnameByUser(@UFGUsername)',null,'prs_FamilyClubname = dbo.fnGetClubnameByUser(@UFGUsername)','prs_FamilyClubname = dbo.fnGetClubnameByUser(@UFGUsername)'
FROM tblUFGRoles
WHERE urr_RoleName LIKE '%Club'

INSERT tblUFGRowRights
(urr_TableName,urr_RoleName,urr_SQLSelectExpression,urr_SQLInsertExpression,urr_SQLUpdateExpression,urr_SQLDeleteExpression)
SELECT 'tblPersons',urr_Rolename,'1 = 1','1 = 1','1 = 1','1 = 1'
FROM tblUFGRoles
WHERE urr_RoleName LIKE '%Bond'

INSERT tblUFGRowRights
(urr_TableName,urr_RoleName,urr_SQLSelectExpression,urr_SQLUpdateExpression) VALUES
('tblPersons','Leden','prs_FamilyName IN (SELECT prs_FamilyName FROM tblPersons WHERE prs_Username = @UFGUsername)','prs_Username = @UFGUsername')

UPDATE tblUFGForeignKeyColumns
SET ufc_AdditionalWhere = 'dbo.fnGetClubnameByUser(@UFGUsername) IN (clb_Name, ''*'')'
WHERE ufc_ForeignKeyName = 'FK_prs_FamilyName'
AND ufc_TableName = 'tblPersons'
AND ufc_ColumnName = 'prs_FamilyClubName'
AND ufc_ReferenceTableName = 'tblFamilies'
AND ufc_ReferenceColumnName = 'fam_ClubName'

UPDATE tblUFGForeignKeyColumns
SET ufc_AdditionalWhere = 'dbo.fnGetClubnameByUser(@UFGUsername) IN (fam_ClubName, ''*'')'
WHERE ufc_ForeignKeyName = 'FK_prs_FamilyName'
AND ufc_TableName = 'tblPersons'
AND ufc_ColumnName = 'prs_FamilyName'
AND ufc_ReferenceTableName = 'tblFamilies'
AND ufc_ReferenceColumnName = 'fam_FamilyName'

-- Unique index op username, behalve voor NULL values
CREATE UNIQUE NONCLUSTERED INDEX IX_prs_Username ON tblPersons (prs_Username) WHERE prs_Username IS NOT NULL;
GO

-- Controle Status geroyeerd
ALTER TABLE [dbo].[tblPersons]  WITH CHECK ADD  CONSTRAINT [CK_tblPersons_StateActive] CHECK  ((NOT ([prs_Active]=(1) AND [prs_State]='Geroyeerd')))
GO

-- Controle RefereeNr Verplicht indien Referee
ALTER TABLE [dbo].[tblPersons]  WITH CHECK ADD  CONSTRAINT [CK_tblPersons_ChkReferee] CHECK  (([prs_Referee]=(0) OR NOT ([prs_RefereeNr] = 'Aangevraagd' OR [prs_RefereeNr] IS NULL)))
GO

-- RefereeNr unique
CREATE UNIQUE INDEX UK_prs_RefereeNr ON tblPersons (prs_RefereeNr)
WHERE prs_RefereeNr IS NOT NULL AND prs_RefereeNr <> 'Aangevraagd'
GO

-- RefereeNr unique
CREATE UNIQUE INDEX UK_prs_FederationNr ON tblPersons (prs_FederationNr)
WHERE prs_FederationNr IS NOT NULL
GO

INSERT tblUFGMessages (umm_MessageName, umm_Languagename, umm_Message) VALUES
('UK_prs_FederationNr', 'Nederlands', 'Bondsnummer bestaat al'),
('UK_prs_RefereeNr', 'Nederlands', 'Bondsnummer bestaat al')